package com.xiaoma.tpo.dao;

import java.io.UnsupportedEncodingException;
import java.util.ArrayList;
import java.util.List;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Repository;

import com.xiaoma.tpo.baseDao.BaseDaoImpl;
import com.xiaoma.tpo.baseDao.DefaultRowMapper;
import com.xiaoma.tpo.domain.ClsSentence;
import com.xiaoma.tpo.dto.ClsSentenceVo;
import com.xiaoma.tpo.utils.Pager;
import com.xiaoma.tpo.utils.PagingOrder;

@Repository
public class ClsSentenceDao extends BaseDaoImpl<ClsSentence>{

    private static final Logger LOG = LoggerFactory.getLogger(ClsSentenceDao.class);
    @SuppressWarnings("unchecked")
    public List<ClsSentence> findSentencesByGateId(int gateId) {
        String sql = "select a.* from cls_sentence a, cls_gate_sentence b where a.id = b.cls_sentence_id and b.cls_gate_id = ?";//inner join
//        String sql = "select a.* from cls_sentence a left join cls_gate_sentence b on a.id = b.cls_sentence_id where b.cls_gate_id = ?";//left join
        LOG.info("SQL:{} gateId:{}", sql, gateId);
        List<?> result = jdbcTemplate.query(sql, new DefaultRowMapper(ClsSentence.class, getNameHandler()),gateId);
        return (List<ClsSentence>)result;
    }

    @SuppressWarnings("unchecked")
    public Pager findSentenceByArgs(String sentence ,String code,int page,int rows){
        StringBuffer sql = new StringBuffer();
        StringBuffer sqlTotal = new StringBuffer();
        List<Object> params = new ArrayList<Object>();
        sql.append("select id,code,sentence,sentence_cn,audio_code,audio1,audio2,audio3,audio4,audio_score from cls_sentence where 1=1 ");
        sqlTotal.append("select count(id) from cls_sentence where 1=1 ");
        if(sentence!=null && sentence.length()>0){
            sql.append(" and sentence like ? ");
            params.add("%"+sentence+"%");

            sqlTotal.append(" and sentence like ? ");
        }
        if(code!=null && code.length()>0){
            sql.append(" and code like ? ");
            params.add("%" + code + "%");
            sqlTotal.append(" and code like ? ");
        }

        sql.append(" limit ?,?");
        int totalCount = jdbcTemplate.queryForInt(sqlTotal.toString(),params.toArray());
        params.add((page-1)*rows);
        params.add(rows);
        LOG.info("SQL:{}", sql.toString());
        Pager pager = new Pager();
        pager.setTotalCount(totalCount);
        List<?> list = jdbcTemplate.query(sql.toString(),params.toArray(), new DefaultRowMapper(ClsSentence.class, getNameHandler()));
        pager.setList(list);
        return pager;
    }

	public List<ClsSentenceVo> findPCSentencesByGateId(Integer gateId) {
		String sql = "select a.* from cls_sentence a, cls_gate_sentence b where a.id = b.cls_sentence_id and b.cls_gate_id = ?";//inner join
//      String sql = "select a.* from cls_sentence a left join cls_gate_sentence b on a.id = b.cls_sentence_id where b.cls_gate_id = ?";//left join
      LOG.info("SQL:{} gateId:{}", sql, gateId);
      List<?> result = jdbcTemplate.query(sql, new DefaultRowMapper(ClsSentenceVo.class, getNameHandler()),gateId);
      return (List<ClsSentenceVo>)result;
	}

	public Pager findWithCondition(Integer page, Integer rows, Integer id,
			String code, String sentence, String sentenceCn) {
		Pager pager = new Pager();
		Integer startNum = 0;
		if(page != null && page >0){
			startNum = (page-1)*rows;
			pager.setCurPage(rows);
		}
		Integer perNum = 50;
		if(rows != null){
			perNum = rows;
			pager.setItemsPerPage(rows);
		}else{
			pager.setItemsPerPage(50);
		}
		String countSql = " select count(*)"
				+ " from cls_sentence where 1=1";
		String sql = "select * "
				+ " from cls_sentence where 1=1";
		if(id != null){
			sql = sql + " and id = "+id;
			countSql = countSql + " and id = "+id;
		}
		if(code != null && code.length() >0){
			sql = sql +" and code like '%"+code.trim()+"%'";
			countSql = countSql +" and code like '%"+code.trim()+"%'";
		}
		if(sentence != null && sentence.length() > 0){
			sql = sql + " and sentence like '%"+sentence.trim()+"%'";
			countSql = countSql + " and sentence like '%"+sentence.trim()+"%'";
		}
		if(sentenceCn != null && sentenceCn.length() >0){
			sql = sql + " and sentence_cn like '%"+sentenceCn.trim()+"%'";
			countSql = countSql + " and sentence_cn like '%"+sentenceCn.trim()+"%'";
		}
		sql += " order by id  limit "+startNum+","+perNum;
		countSql += " order by id  ";
		Integer totalCount = jdbcTemplate.queryForInt(countSql);
		List<?> lClsSentence = jdbcTemplate.query(sql, new DefaultRowMapper(ClsSentence.class, getNameHandler()));
		pager.setList(lClsSentence);
		pager.setTotalCount(totalCount);
		return pager;
	}
   
}
